﻿using DAL;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Windows.Forms;
using System.Data;

namespace MainOP
{
    public partial class FormDataSelectelEctricity2 : Form
    {
        public FormDataSelectelEctricity2()
        {
            InitializeComponent();
        }
        //List<oductDataToDJModel2> objProductData = new List<oductDataToDJModel2>();
        private void button1_Click(object sender, EventArgs e)
        {
            ////查询前先清空数据集
            //objProductData.Clear();
            //清空表格再查询
            while (this.dataGridView1.Rows.Count != 0)
            {
                this.dataGridView1.DataSource = null;
            }
            string dateTStart = this.dateTimePicker1.Text;
            string dateTOff = this.dateTimePicker2.Text;

            char[] chs1 = { '年', '月', '日' };
            string[] str1 = dateTStart.Split(chs1, StringSplitOptions.RemoveEmptyEntries);
            string datetimeStart = string.Join("/", str1);

            char[] chs2 = { '年', '月', '日' };
            string[] str2 = dateTOff.Split(chs2, StringSplitOptions.RemoveEmptyEntries);
            string datetimeOff = string.Join("/", str2);

            //where[完成时间] Between'" + dateTimePicker1.Text + "'and'" + dateTimePicker2.Text + "'
            //FROM OPENROWSET('SQLOLEDB','192.168.1.161';'sa';'943937821',GEMINI.dbo.productDataToDJ) A JOIN OPEN
            //string sql = "SELECT [产品下线时间],[产品型号],[总成码],[托盘号],[testDoneDate], [typeName], [serialNumber], [操作员], A.[总结果], [新风内循环电压], [新风内循环角度], [新风内循环结果], [新风外循环电压], [新风外循环角度], [新风外循环结果], [新风出厂电压], [新风出厂角度], [新风出厂结果], [循环内循环电压], [循环内循环角度], [循环内循环结果], [循环外循环电压], [循环外循环角度], [循环外循环结果], [循环出厂电压], [循环出厂角度], [循环出厂结果], [鼓风机一档占空比], [鼓风机一档电压], [鼓风机一档电流], [鼓风机一档振动X], [鼓风机一档振动Y],  [鼓风机一档结果], [鼓风机二档占空比], [鼓风机二档电压], [鼓风机二档电流], [鼓风机二档振动X], [鼓风机二档振动Y],  [鼓风机二档结果], [鼓风机三档占空比], [鼓风机三档电压], [鼓风机三档电流], [鼓风机三档振动X], [鼓风机三档振动Y], [鼓风机三档结果], [鼓风机四档占空比], [鼓风机四档电压], [鼓风机四档电流], [鼓风机四档振动X], [鼓风机四档振动Y],[NVH], [鼓风机四档结果], [蒸发器温度], [温度压力传感器], [CCU软件版本号], [CCU硬件版本号], [EXV], [SOTXV], [CAN1结果], [CAN2结果], [LIN结果], [CCU标定结果] FROM OPENROWSET('SQLOLEDB','192.168.1.161';'sa';'943937821',GEMINI.dbo.productDataToDJ) A JOIN OPENROWSET('SQLOLEDB','192.168.1.150'; 'sa'; '943937821',GEMINI.dbo.productData) B ON A.serialNumber = B.ID where[产品下线时间] Between '" + dateTimePicker1.Text + "' and '" + dateTimePicker2.Text + "' order by[产品下线时间] ASC";
            string sql = "select * from BAIDU.dbo.productDataToDJ where[完成时间] Between'" + datetimeStart + "'and'" + datetimeOff + "' order by [完成时间] ASC";
            string connstr = "server=172.16.150.1;DataBase=BAIDU;Uid=sa;Pwd=943937821";     //server=172.16.150.1;

            SqlConnection conn = new SqlConnection(connstr);
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataAdapter da = new SqlDataAdapter(cmd); //创建数据适配器对象
            DataSet ds = new DataSet();//创建一个内存数据集

            conn.Open();
            da.Fill(ds);  //使用数据适配器填充数据集

            dataGridView1.DataSource = ds.Tables[0];

            //objProductData = oductDataToDJ2Func.GetData(sql, true);
            //dataGridView1.AutoGenerateColumns = true;
            //dataGridView1.DataSource = objProductData;
        }

        private void button2_Click(object sender, EventArgs e)      //未使用，功能也未完成
        {
            ////查询前先清空数据集
            //objProductData.Clear();
            ////清空表格再查询
            //while (this.dataGridView1.Rows.Count != 1)
            //{
            //    this.dataGridView1.DataSource = null;
            //}
            //if (comboBox1.Text != "")
            //{
            //    string sql = "SELECT [产品下线时间],[产品型号],[总成码],[托盘号],[testDoneDate], [typeName], [serialNumber], [操作员], A.[总结果], [新风内循环电压], [新风内循环角度], [新风内循环结果], [新风外循环电压], [新风外循环角度], [新风外循环结果], [新风出厂电压], [新风出厂角度], [新风出厂结果], [循环内循环电压], [循环内循环角度], [循环内循环结果], [循环外循环电压], [循环外循环角度], [循环外循环结果], [循环出厂电压], [循环出厂角度], [循环出厂结果], [鼓风机一档占空比], [鼓风机一档电压], [鼓风机一档电流], [鼓风机一档振动X], [鼓风机一档振动Y], [鼓风机一档结果], [鼓风机二档占空比], [鼓风机二档电压], [鼓风机二档电流], [鼓风机二档振动X], [鼓风机二档振动Y], [鼓风机二档结果], [鼓风机三档占空比], [鼓风机三档电压], [鼓风机三档电流], [鼓风机三档振动X], [鼓风机三档振动Y], [鼓风机三档结果], [鼓风机四档占空比], [鼓风机四档电压], [鼓风机四档电流], [鼓风机四档振动X], [鼓风机四档振动Y], [NVH],[鼓风机四档结果], [蒸发器温度], [温度压力传感器], [CCU软件版本号], [CCU硬件版本号], [EXV], [SOTXV], [CAN1结果], [CAN2结果], [LIN结果], [CCU标定结果] FROM OPENROWSET('SQLOLEDB','192.168.1.161';'sa';'943937821',GEMINI.dbo.productDataToDJ) A JOIN OPENROWSET('SQLOLEDB','192.168.1.150'; 'sa'; '943937821',GEMINI.dbo.productData) B ON A.serialNumber = B.ID where " + comboBox1.Text + "='" + textBox1.Text + "' order by[产品下线时间] ASC";
            //    objProductData = oductDataToDJ2Func.GetData(sql, true);
            //}
            //else
            //{
            //    string sql = "SELECT [产品下线时间],[产品型号],[总成码],[托盘号],[testDoneDate], [typeName], [serialNumber], [操作员], A.[总结果], [新风内循环电压], [新风内循环角度], [新风内循环结果], [新风外循环电压], [新风外循环角度], [新风外循环结果], [新风出厂电压], [新风出厂角度], [新风出厂结果], [循环内循环电压], [循环内循环角度], [循环内循环结果], [循环外循环电压], [循环外循环角度], [循环外循环结果], [循环出厂电压], [循环出厂角度], [循环出厂结果], [鼓风机一档占空比], [鼓风机一档电压], [鼓风机一档电流], [鼓风机一档振动X], [鼓风机一档振动Y], [鼓风机一档结果], [鼓风机二档占空比], [鼓风机二档电压], [鼓风机二档电流], [鼓风机二档振动X], [鼓风机二档振动Y], [鼓风机二档结果], [鼓风机三档占空比], [鼓风机三档电压], [鼓风机三档电流], [鼓风机三档振动X], [鼓风机三档振动Y], [鼓风机三档结果], [鼓风机四档占空比], [鼓风机四档电压], [鼓风机四档电流], [鼓风机四档振动X], [鼓风机四档振动Y], [NVH],[鼓风机四档结果], [蒸发器温度], [温度压力传感器], [CCU软件版本号], [CCU硬件版本号], [EXV], [SOTXV], [CAN1结果], [CAN2结果], [LIN结果], [CCU标定结果] FROM OPENROWSET('SQLOLEDB','192.168.1.161';'sa';'943937821',GEMINI.dbo.productDataToDJ) A JOIN OPENROWSET('SQLOLEDB','192.168.1.150'; 'sa'; '943937821',GEMINI.dbo.productData) B ON A.serialNumber = B.ID where concat([产品下线时间],[产品型号],[总成码],[托盘号],[testDoneDate], [typeName], [serialNumber], [操作员], A.[总结果], [新风内循环电压], [新风内循环角度], [新风内循环结果], [新风外循环电压], [新风外循环角度], [新风外循环结果], [新风出厂电压], [新风出厂角度], [新风出厂结果], [循环内循环电压], [循环内循环角度], [循环内循环结果], [循环外循环电压], [循环外循环角度], [循环外循环结果], [循环出厂电压], [循环出厂角度], [循环出厂结果], [鼓风机一档占空比], [鼓风机一档电压], [鼓风机一档电流], [鼓风机一档振动X], [鼓风机一档振动Y], [鼓风机一档结果], [鼓风机二档占空比], [鼓风机二档电压], [鼓风机二档电流], [鼓风机二档振动X], [鼓风机二档振动Y],[鼓风机二档结果], [鼓风机三档占空比], [鼓风机三档电压], [鼓风机三档电流], [鼓风机三档振动X], [鼓风机三档振动Y],[鼓风机三档结果], [鼓风机四档占空比], [鼓风机四档电压], [鼓风机四档电流], [鼓风机四档振动X], [鼓风机四档振动Y], [NVH],[鼓风机四档结果], [蒸发器温度], [温度压力传感器], [CCU软件版本号], [CCU硬件版本号], [EXV], [SOTXV], [CAN1结果], [CAN2结果], [LIN结果], [CCU标定结果]) like '%" + textBox1.Text + "%' order by[产品下线时间] ASC";
            //    objProductData = oductDataToDJ2Func.GetData(sql,true);
            //}

            //this.dataGridView1.AutoGenerateColumns = false;
            //this.dataGridView1.DataSource = objProductData;
        }

        private void button3_Click(object sender, EventArgs e)
        {
            ////创建Excel工作簿和工作表
            //XSSFWorkbook workbook = new XSSFWorkbook();
            //ISheet newsheet = workbook.CreateSheet("蒸发箱电检记录查询");

            ////创建Excel工作表的列标题
            //IRow rowHeader = newsheet.CreateRow(0);
            //for (int i = 0; i < cycCell.Count; i++)
            //{
            //    rowHeader.CreateCell(0, CellType.String).SetCellValue("产品下线时间");
            //    rowHeader.CreateCell(1, CellType.String).SetCellValue("typeName");
            //    rowHeader.CreateCell(2, CellType.String).SetCellValue("总成码");
            //    rowHeader.CreateCell(3, CellType.String).SetCellValue("托盘号");
            //    rowHeader.CreateCell(4, CellType.String).SetCellValue("操作员");
            //    rowHeader.CreateCell(5, CellType.String).SetCellValue(" 总结果");
            //    rowHeader.CreateCell(6, CellType.String).SetCellValue("内循环-新风电压");
            //    rowHeader.CreateCell(7, CellType.String).SetCellValue("内循环-新风角度");
            //    rowHeader.CreateCell(8, CellType.String).SetCellValue("内循环-循环电压");
            //    rowHeader.CreateCell(9, CellType.String).SetCellValue("内循环-循环角度");
            //    rowHeader.CreateCell(10, CellType.String).SetCellValue("外循环-新风电压");
            //    rowHeader.CreateCell(11, CellType.String).SetCellValue("外循环-新风角度");
            //    rowHeader.CreateCell(12, CellType.String).SetCellValue("外循环-循环电压");
            //    rowHeader.CreateCell(13, CellType.String).SetCellValue("外循环-循环角度");
            //    rowHeader.CreateCell(14, CellType.String).SetCellValue("出厂-新风电压");
            //    rowHeader.CreateCell(15, CellType.String).SetCellValue("出厂-新风角度");
            //    rowHeader.CreateCell(16, CellType.String).SetCellValue("出厂-循环电压");
            //    rowHeader.CreateCell(17, CellType.String).SetCellValue("出厂-循环角度");
            //    rowHeader.CreateCell(18, CellType.String).SetCellValue(" 鼓风机一档占空比");
            //    rowHeader.CreateCell(19, CellType.String).SetCellValue("鼓风机一档电流");
            //    rowHeader.CreateCell(20, CellType.String).SetCellValue("鼓风机一档振动X");
            //    rowHeader.CreateCell(21, CellType.String).SetCellValue("鼓风机一档振动Y");
            //    rowHeader.CreateCell(22, CellType.String).SetCellValue(" 鼓风机二档占空比");
            //    rowHeader.CreateCell(23, CellType.String).SetCellValue("鼓风机二档电流");
            //    rowHeader.CreateCell(24, CellType.String).SetCellValue("鼓风机二档振动X");
            //    rowHeader.CreateCell(25, CellType.String).SetCellValue("鼓风机二档振动Y");
            //    rowHeader.CreateCell(26, CellType.String).SetCellValue(" 鼓风机三档占空比");
            //    rowHeader.CreateCell(27, CellType.String).SetCellValue("鼓风机三档电流");
            //    rowHeader.CreateCell(28, CellType.String).SetCellValue("鼓风机三档振动X");
            //    rowHeader.CreateCell(29, CellType.String).SetCellValue("鼓风机三档振动Y");
            //    rowHeader.CreateCell(30, CellType.String).SetCellValue(" 鼓风机四档占空比");
            //    rowHeader.CreateCell(31, CellType.String).SetCellValue("鼓风机四档电流");
            //    rowHeader.CreateCell(32, CellType.String).SetCellValue("鼓风机四档振动X");
            //    rowHeader.CreateCell(33, CellType.String).SetCellValue("鼓风机四档振动Y");
            //    rowHeader.CreateCell(34, CellType.String).SetCellValue(" 蒸发器温度");
            //    rowHeader.CreateCell(35, CellType.String).SetCellValue("温度压力传感器");
            //    rowHeader.CreateCell(36, CellType.String).SetCellValue("CCU软件版本号");
            //    rowHeader.CreateCell(37, CellType.String).SetCellValue("CCU硬件版本号");
            //    rowHeader.CreateCell(38, CellType.String).SetCellValue("EXV");
            //    rowHeader.CreateCell(39, CellType.String).SetCellValue("SOTXV");
            //    rowHeader.CreateCell(40, CellType.String).SetCellValue("CAN1结果");
            //    rowHeader.CreateCell(41, CellType.String).SetCellValue("CAN2结果");
            //    rowHeader.CreateCell(42, CellType.String).SetCellValue("LIN结果");
            //    rowHeader.CreateCell(43, CellType.String).SetCellValue("CCU标定结果");
            //    rowHeader.CreateCell(44, CellType.String).SetCellValue("NVH结果");

            //}

            //for (int i = 0; i < objProductData.Count; i++)
            //{
            //    IRow row = newsheet.CreateRow(i + 1);
            //    row.CreateCell(0, CellType.String).SetCellValue(objProductData[i].产品下线时间);
            //    row.CreateCell(1, CellType.String).SetCellValue(objProductData[i].typeName);
            //    row.CreateCell(2, CellType.String).SetCellValue(objProductData[i].总成码);
            //    row.CreateCell(3, CellType.String).SetCellValue(objProductData[i].托盘号);
            //    row.CreateCell(4, CellType.String).SetCellValue(objProductData[i].操作员);
            //    row.CreateCell(5, CellType.String).SetCellValue(objProductData[i].总结果);
            //    row.CreateCell(6, CellType.String).SetCellValue(objProductData[i].新风内循环电压);
            //    row.CreateCell(7, CellType.String).SetCellValue(objProductData[i].新风内循环角度);
            //    row.CreateCell(8, CellType.String).SetCellValue(objProductData[i].循环内循环电压);
            //    row.CreateCell(9, CellType.String).SetCellValue(objProductData[i].循环内循环角度);
            //    row.CreateCell(10, CellType.String).SetCellValue(objProductData[i].新风外循环电压);
            //    row.CreateCell(11, CellType.String).SetCellValue(objProductData[i].新风外循环角度);
            //    row.CreateCell(12, CellType.String).SetCellValue(objProductData[i].循环外循环电压);
            //    row.CreateCell(13, CellType.String).SetCellValue(objProductData[i].新风出厂电压);
            //    row.CreateCell(14, CellType.String).SetCellValue(objProductData[i].新风出厂角度);
            //    row.CreateCell(15, CellType.String).SetCellValue(objProductData[i].循环外循环角度);
            //    row.CreateCell(16, CellType.String).SetCellValue(objProductData[i].循环出厂电压);
            //    row.CreateCell(17, CellType.String).SetCellValue(objProductData[i].循环出厂角度);
            //    row.CreateCell(18, CellType.String).SetCellValue(objProductData[i].鼓风机一档占空比);
            //    row.CreateCell(19, CellType.String).SetCellValue(objProductData[i].鼓风机一档电流);
            //    row.CreateCell(20, CellType.String).SetCellValue(objProductData[i].鼓风机一档振动X);
            //    row.CreateCell(21, CellType.String).SetCellValue(objProductData[i].鼓风机一档振动Y);
            //    row.CreateCell(22, CellType.String).SetCellValue(objProductData[i].鼓风机二档占空比);
            //    row.CreateCell(23, CellType.String).SetCellValue(objProductData[i].鼓风机二档电流);
            //    row.CreateCell(24, CellType.String).SetCellValue(objProductData[i].鼓风机二档振动X);
            //    row.CreateCell(25, CellType.String).SetCellValue(objProductData[i].鼓风机二档振动Y);
            //    row.CreateCell(26, CellType.String).SetCellValue(objProductData[i].鼓风机三档占空比);
            //    row.CreateCell(27, CellType.String).SetCellValue(objProductData[i].鼓风机三档电流);
            //    row.CreateCell(28, CellType.String).SetCellValue(objProductData[i].鼓风机三档振动X);
            //    row.CreateCell(29, CellType.String).SetCellValue(objProductData[i].鼓风机三档振动Y);
            //    row.CreateCell(30, CellType.String).SetCellValue(objProductData[i].鼓风机四档占空比);
            //    row.CreateCell(31, CellType.String).SetCellValue(objProductData[i].鼓风机四档电流);
            //    row.CreateCell(32, CellType.String).SetCellValue(objProductData[i].鼓风机四档振动X);
            //    row.CreateCell(33, CellType.String).SetCellValue(objProductData[i].鼓风机四档振动Y);
            //    row.CreateCell(34, CellType.String).SetCellValue(objProductData[i].蒸发器温度);
            //    row.CreateCell(35, CellType.String).SetCellValue(objProductData[i].温度压力传感器);
            //    row.CreateCell(36, CellType.String).SetCellValue(objProductData[i].CCU软件版本号);
            //    row.CreateCell(37, CellType.String).SetCellValue(objProductData[i].CCU硬件版本号);
            //    row.CreateCell(38, CellType.String).SetCellValue(objProductData[i].EXV);
            //    row.CreateCell(39, CellType.String).SetCellValue(objProductData[i].SOTXV);
            //    row.CreateCell(40, CellType.String).SetCellValue(objProductData[i].CAN1结果);
            //    row.CreateCell(41, CellType.String).SetCellValue(objProductData[i].CAN2结果);
            //    row.CreateCell(42, CellType.String).SetCellValue(objProductData[i].LIN结果);
            //    row.CreateCell(43, CellType.String).SetCellValue(objProductData[i].CCU标定结果);
            //    row.CreateCell(44, CellType.String).SetCellValue(objProductData[i].NVH);


            //}

            ////选择文件位置
            //SaveFileDialog sfd = new SaveFileDialog();
            //sfd.InitialDirectory = @"C:\Users\SpringRain\Desktop";
            //sfd.Title = "请选择要保存的文件";
            //sfd.Filter = "Excel表格|*.xlsx";
            //sfd.ShowDialog(this);
            //string path = sfd.FileName;

            ////将输出流写入Excel工作簿
            //try
            //{
            //    using (FileStream fSwrite = File.OpenWrite(path))
            //    {
            //        workbook.Write(fSwrite);
            //        MessageBox.Show("保存成功");

            //    }
            //}
            //catch
            //{

            //}
            SaveFileDialog saveFileDialog = new SaveFileDialog();

            saveFileDialog.InitialDirectory = @"C:\Users\SpringRain\Desktop";
            saveFileDialog.Title = "请选择要保存的文件";
            //  sfd.Filter = "Excel表格|*.xlsx";
            saveFileDialog.Filter = "execel文件|*.xls";
            // saveFileDialog.ShowDialog(this);
            //XSSFWorkbook workbook = new XSSFWorkbook();
            //ISheet newsheet = workbook.CreateSheet("追溯记录");
            if (saveFileDialog.ShowDialog() == DialogResult.OK && saveFileDialog.FileName != "")
            {
                Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
                if (xlApp == null)
                {
                    MessageBox.Show("未安装Microsoft.Office");
                    return;
                }
                Microsoft.Office.Interop.Excel.Workbooks workbookA = xlApp.Workbooks;
                Microsoft.Office.Interop.Excel.Workbook workbook = workbookA.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
                Microsoft.Office.Interop.Excel.Worksheet worksheet = workbook.Worksheets[1];//表单
                //写入标题
                for (int i = 0; i < dataGridView1.ColumnCount; i++)
                {
                    worksheet.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;
                }
                //写入数值
                for (int r = 0; r < dataGridView1.RowCount; r++)
                {
                    for (int i = 0; i < dataGridView1.ColumnCount; i++)
                    {
                        worksheet.Cells[r + 2, i + 1] = dataGridView1.Rows[r].Cells[i].Value;
                    }
                }
                worksheet.Columns.EntireColumn.AutoFit();
                MessageBox.Show("保存成功");
                workbook.Saved = true;
                workbook.SaveCopyAs(saveFileDialog.FileName);
                xlApp.Quit();
            }
        }
        //未使用
        //List<string> cycCell = new List<string>();
        private void FormDataSelectelEctricity2_Load(object sender, EventArgs e)
        { 
            //string sql = "select name from syscolumns where id = object_id(N'productDataToDJ') order by charindex(name, '[testDoneDate], [typeName], [serialNumber], [操作员], [总结果], [新风内循环电压], [新风内循环角度], [新风内循环结果], [新风外循环电压], [新风外循环角度], [新风外循环结果], [新风出厂电压], [新风出厂角度], [新风出厂结果], [循环内循环电压], [循环内循环角度], [循环内循环结果], [循环外循环电压], [循环外循环角度], [循环外循环结果], [循环出厂电压], [循环出厂角度], [循环出厂结果], [鼓风机一档占空比], [鼓风机一档电压], [鼓风机一档电流], [鼓风机一档振动X], [鼓风机一档振动Y], [鼓风机一档NVH结果], [鼓风机一档结果], [鼓风机二档占空比], [鼓风机二档电压], [鼓风机二档电流], [鼓风机二档振动X], [鼓风机二档振动Y], [鼓风机二档NVH结果], [鼓风机二档结果], [鼓风机三档占空比], [鼓风机三档电压], [鼓风机三档电流], [鼓风机三档振动X], [鼓风机三档振动Y], [鼓风机三档NVH结果], [鼓风机三档结果], [鼓风机四档占空比], [鼓风机四档电压], [鼓风机四档电流], [鼓风机四档振动X], [鼓风机四档振动Y], [NVH], [鼓风机四档结果], [蒸发器温度], [温度压力传感器], [CCU软件版本号], [CCU硬件版本号], [EXV], [SOTXV], [CAN1结果], [CAN2结果], [LIN结果], [CCU标定结果]')";
            //SqlDataReader rowname = SqlHelperDJ2.GetReader(sql);
            //comboBox1.Items.Add("产品下线时间");
            //comboBox1.Items.Add("总成码");
            //comboBox1.Items.Add("托盘号");
            //cycCell.Add("产品下线时间");
            //cycCell.Add("总成码");
            //cycCell.Add("托盘号");
            //while (rowname.Read())
            //{
            //    comboBox1.Items.Add(rowname["name"].ToString());
            //    cycCell.Add(rowname["name"].ToString());
            //}
            //rowname.Close();
        }
    }
}
